import pymysql.cursors
from pathlib import Path

connection = pymysql.connect(host='localhost',
                            user = 'root',
                            password='123456',
                            database='test',
                            cursorclass=pymysql.cursors.DictCursor)

with connection:
    with connection.cursor() as cursor:
        sql = "select id, file_path from `file`"
        cursor.execute(sql)
        result = cursor.fetchall()
        # 遍历所有记录，判断文件是否存在
        not_exist_file_id = []
        for file in result:
            file_path = file['file_path']
            p = Path(file_path)
            if not p.exists():
                not_exist_file_id.append(file['id'])
                print(file_path)
        # 根据id删除数据库中记录
        for id in not_exist_file_id:
            del_sql = "delete from `file` where id = %s"
            cursor.execute(del_sql, (id,))
        connection.commit()
    
        print(len(not_exist_file_id))